
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetLastRTUCOI]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].GetLastRTUCOI
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************
* Name:			GetLastRTUCOI
* Purpose:		Retrieves the last submitted COI for the specified person
*
* PARAMETERS
* Name    		Description					
* -------------  	-------------------------------------------
* @admPersonId		Person Id
******************************************************************/

CREATE Procedure [dbo].GetLastRTUCOI (
		@admPersonId		INTEGER	
)
AS
BEGIN

	SET NOCOUNT ON
	
	DECLARE @LastRTUCOICount INTEGER
	
	SELECT TOP 1 @LastRTUCOICount = COI.Id FROM ConflictOfInterest COI 
		INNER JOIN RefCode ON COI.[Status] = RefCode.RefCode
		LEFT JOIN COIReportingPeriods CRP ON CRP.Id = COI.ReportingPeriodId
	WHERE 
		AdmPersonId = @admPersonId
		AND COI.COITypeId = 2
		AND [Status] NOT IN ('COISTATUS_SUBMITTED', 'COISTATUS_REVIEWED','COISTATUS_TERMINATED','COISTATUS_CLOSED')
	ORDER BY DateSubmitted DESC
	
	--RTU COI is not availble, so get the LastSubmitted Annual COI
	IF(ISNULL(@LastRTUCOICount,0) < 1)
	BEGIN
		EXEC GetLastSubmittedCOI @admPersonId = @admPersonId
	END
	ELSE
	BEGIN
		SELECT TOP 1 COI.Id,
			AdmPersonId,
			FwkDomainUserId,
			LastNotified,
			LastModified,
			DateSubmitted,
			DateReviewed,		
			HasOutsideActivities,
			HasEquityInterests,
			HasFamilyAssociations,
			Royalty,
			OtherActivity,
			PolicyViolation,
			AggregateEquity,
			[Status],
			RefCode.RefMeaning AS StatusMeaning, 
			DocumentId,
			ReportingPeriodId,
			ReportingPeriod,
			COI.COITypeId
		FROM ConflictOfInterest COI 
			INNER JOIN RefCode ON COI.[Status] = RefCode.RefCode
			LEFT JOIN COIReportingPeriods CRP ON CRP.Id = COI.ReportingPeriodId
		WHERE 
		AdmPersonId = @admPersonId
		AND COI.COITypeId = 2
		AND [Status] NOT IN ('COISTATUS_SUBMITTED', 'COISTATUS_REVIEWED','COISTATUS_TERMINATED','COISTATUS_CLOSED')
		ORDER BY DateSubmitted DESC
	END		
	
END


GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF  



--EXEC GetLastRTUCOI @admPersonId = 263550 --208960

